import pymysql
import json
from typing import List, Dict, Any, Optional, Tuple
from datetime import datetime


class KindergartenDBTool:
    def __init__(self, host, user, password, database, port=3306):
        self.db_config = {
            'host': host,
            'user': user,
            'password': password,
            'database': database,
            'port': port,
            'charset': 'utf8mb4',
            'cursorclass': pymysql.cursors.DictCursor
        }
        self.connection = None

    def connect(self):
        """连接到数据库"""
        try:
            self.connection = pymysql.connect(**self.db_config)
            print("数据库连接成功")
            return True
        except Exception as e:
            print(f"数据库连接失败: {e}")
            return False

    def disconnect(self):
        """断开数据库连接"""
        if self.connection:
            self.connection.close()
            print("数据库连接已关闭")

    def get_parent_by_phone(self, phone: str) -> Optional[Dict[str, Any]]:
        """根据手机号查询家长信息"""
        try:
            with self.connection.cursor() as cursor:
                sql = """
                    SELECT * FROM kind_tb_parent 
                    WHERE parent_phone = %s AND is_delete = 0
                """
                cursor.execute(sql, (phone,))
                result = cursor.fetchone()
                return result
        except Exception as e:
            print(f"查询家长信息失败: {e}")
            return None

    def get_children_by_parent_id(self, parent_id: str) -> List[Dict[str, Any]]:
        """根据家长ID查询孩子信息，增加child_status=1的条件"""
        try:
            with self.connection.cursor() as cursor:
                sql = """
                    SELECT * FROM kind_tb_child 
                    WHERE parent_id = %s 
                      AND is_delete = 0
                      AND child_status = 1
                """
                cursor.execute(sql, (parent_id,))
                results = cursor.fetchall()
                return results
        except Exception as e:
            print(f"查询孩子信息失败: {e}")
            return []

    def get_kindergarten_by_id(self, kindergarten_id: str) -> Optional[Dict[str, Any]]:
        """根据幼儿园ID查询幼儿园信息"""
        try:
            with self.connection.cursor() as cursor:
                sql = """
                    SELECT * FROM kind_tb_kindergarten 
                    WHERE kindergarten_id = %s AND is_delete = 0
                """
                cursor.execute(sql, (kindergarten_id,))
                result = cursor.fetchone()
                return result
        except Exception as e:
            print(f"查询幼儿园信息失败: {e}")
            return None

    def get_classes_by_id(self, classes_id: str) -> Optional[Dict[str, Any]]:
        """根据班级ID查询班级信息"""
        try:
            with self.connection.cursor() as cursor:
                sql = """
                    SELECT * FROM kind_tb_classes 
                    WHERE classes_id = %s AND is_delete = 0
                """
                cursor.execute(sql, (classes_id,))
                result = cursor.fetchone()
                return result
        except Exception as e:
            print(f"查询班级信息失败: {e}")
            return None

    def get_classes_by_kindergarten(self, kindergarten_id: str) -> List[Dict[str, Any]]:
        """根据幼儿园ID查询所有班级信息"""
        try:
            with self.connection.cursor() as cursor:
                sql = """
                    SELECT * FROM kind_tb_classes 
                    WHERE kindergarten_id = %s AND is_delete = 0
                    ORDER BY classes_name
                """
                cursor.execute(sql, (kindergarten_id,))
                results = cursor.fetchall()
                return results
        except Exception as e:
            print(f"查询班级列表失败: {e}")
            return []

    def get_orders_by_parent_id(self, parent_id: str, kindergarten_ids: List[str]) -> List[Dict[str, Any]]:
        """根据家长ID查询订单信息，增加end_date大于当前日期的限制，并且只显示孩子当前所在幼儿园的数据"""
        try:
            if not kindergarten_ids:
                return []

            with self.connection.cursor() as cursor:
                # 构建IN条件的占位符
                placeholders = ','.join(['%s'] * len(kindergarten_ids))
                sql = f"""
                    SELECT * FROM kind_tb_parent_service_order 
                    WHERE parent_id = %s 
                      AND order_status = 2 
                      AND is_delete = 0
                      AND (end_date IS NULL OR end_date > CURDATE())
                      AND kindergarten_id IN ({placeholders})
                """
                # 构建参数列表
                params = [parent_id] + kindergarten_ids
                cursor.execute(sql, params)
                results = cursor.fetchall()
                return results
        except Exception as e:
            print(f"查询订单信息失败: {e}")
            return []

    def get_video_services_by_parent_id(self, parent_id: str, kindergarten_ids: List[str]) -> List[Dict[str, Any]]:
        """根据家长ID查询视频服务信息，增加end_date大于当前日期的限制，并且只显示孩子当前所在幼儿园的数据"""
        try:
            if not kindergarten_ids:
                return []

            with self.connection.cursor() as cursor:
                # 构建IN条件的占位符
                placeholders = ','.join(['%s'] * len(kindergarten_ids))
                sql = f"""
                    SELECT * FROM kind_tb_video_service_parent 
                    WHERE parent_id = %s 
                      AND is_delete = 0
                      AND (end_date IS NULL OR end_date > CURDATE())
                      AND kindergarten_id IN ({placeholders})
                """
                # 构建参数列表
                params = [parent_id] + kindergarten_ids
                cursor.execute(sql, params)
                results = cursor.fetchall()
                return results
        except Exception as e:
            print(f"查询视频服务信息失败: {e}")
            return []

    def update_order_classes_id(self, order_id: str, new_classes_id: str, child_ids_json: str) -> bool:
        """更新订单表中的classes_id，并同时更新kind_tb_child表中的classes_id"""
        try:
            with self.connection.cursor() as cursor:
                # 解析child_ids JSON
                try:
                    child_ids = json.loads(child_ids_json)
                    if not isinstance(child_ids, list):
                        print(f"订单 {order_id} 的child_ids格式错误")
                        return False
                except json.JSONDecodeError:
                    print(f"订单 {order_id} 的child_ids JSON解析失败")
                    return False

                # 更新订单表的classes_id
                sql_update_order = """
                    UPDATE kind_tb_parent_service_order 
                    SET classes_id = %s, update_time = NOW() 
                    WHERE service_order_id = %s
                """
                cursor.execute(sql_update_order, (new_classes_id, order_id))

                # 更新孩子表的classes_id
                if child_ids:
                    # 构建IN条件的占位符
                    placeholders = ','.join(['%s'] * len(child_ids))
                    sql_update_child = f"""
                        UPDATE kind_tb_child 
                        SET classes_id = %s, update_time = NOW() 
                        WHERE child_id IN ({placeholders})
                    """
                    # 参数：新班级ID + 孩子ID列表
                    params = [new_classes_id] + child_ids
                    cursor.execute(sql_update_child, params)

                self.connection.commit()
                print(f"订单 {order_id} 的classes_id已更新为 {new_classes_id}")
                print(f"同时更新了 {len(child_ids)} 个孩子的班级信息")
                return True
        except Exception as e:
            print(f"更新订单classes_id失败: {e}")
            self.connection.rollback()
            return False

    def update_video_service_classes_id(self, video_service_id: str, new_classes_id: str, child_ids_json: str) -> bool:
        """更新视频服务表中的classes_id，并同时更新kind_tb_child表中的classes_id"""
        try:
            with self.connection.cursor() as cursor:
                # 解析child_ids JSON
                try:
                    child_ids = json.loads(child_ids_json)
                    if not isinstance(child_ids, list):
                        print(f"视频服务 {video_service_id} 的child_ids格式错误")
                        return False
                except json.JSONDecodeError:
                    print(f"视频服务 {video_service_id} 的child_ids JSON解析失败")
                    return False

                # 更新视频服务表的classes_id
                sql_update_service = """
                    UPDATE kind_tb_video_service_parent 
                    SET classes_id = %s, update_time = NOW() 
                    WHERE video_service_parent_id = %s
                """
                cursor.execute(sql_update_service, (new_classes_id, video_service_id))

                # 更新孩子表的classes_id
                if child_ids:
                    # 构建IN条件的占位符
                    placeholders = ','.join(['%s'] * len(child_ids))
                    sql_update_child = f"""
                        UPDATE kind_tb_child 
                        SET classes_id = %s, update_time = NOW() 
                        WHERE child_id IN ({placeholders})
                    """
                    # 参数：新班级ID + 孩子ID列表
                    params = [new_classes_id] + child_ids
                    cursor.execute(sql_update_child, params)

                self.connection.commit()
                print(f"视频服务 {video_service_id} 的classes_id已更新为 {new_classes_id}")
                print(f"同时更新了 {len(child_ids)} 个孩子的班级信息")
                return True
        except Exception as e:
            print(f"更新视频服务classes_id失败: {e}")
            self.connection.rollback()
            return False

    def delete_parent_data(self, phone: str) -> bool:
        """删除家长用户数据"""
        try:
            with self.connection.cursor() as cursor:
                # 查询家长信息
                parent_info = self.get_parent_by_phone(phone)
                if not parent_info:
                    print(f"未找到手机号 {phone} 对应的家长信息")
                    return False

                parent_id = parent_info['parent_id']
                child_id = parent_info.get('child_id')

                # 确认操作
                confirm = input(f"确认删除手机号 {phone} 的所有数据吗？此操作不可恢复！(y/N): ").strip().lower()
                if confirm != 'y':
                    print("操作已取消")
                    return False

                # 删除kind_tb_parent中的数据
                sql_delete_parent = """
                    DELETE FROM kind_tb_parent 
                    WHERE parent_id = %s
                """
                cursor.execute(sql_delete_parent, (parent_id,))
                print(f"已删除家长 {parent_id} 的数据")

                # 如果有child_id，删除kind_tb_child中的数据
                if child_id:
                    sql_delete_child = """
                        DELETE FROM kind_tb_child 
                        WHERE child_id = %s
                    """
                    cursor.execute(sql_delete_child, (child_id,))
                    print(f"已删除孩子 {child_id} 的数据")

                self.connection.commit()
                print("删除操作完成")
                return True
        except Exception as e:
            print(f"删除家长数据失败: {e}")
            self.connection.rollback()
            return False

    def delete_order_data(self, phone: str) -> bool:
        """删除订单记录数据"""
        try:
            with self.connection.cursor() as cursor:
                # 查询家长信息
                parent_info = self.get_parent_by_phone(phone)
                if not parent_info:
                    print(f"未找到手机号 {phone} 对应的家长信息")
                    return False

                parent_id = parent_info['parent_id']

                # 确认操作
                confirm = input(f"确认删除手机号 {phone} 的所有订单数据吗？此操作不可恢复！(y/N): ").strip().lower()
                if confirm != 'y':
                    print("操作已取消")
                    return False

                # 删除kind_tb_parent_service_order中的数据
                sql_delete_orders = """
                    DELETE FROM kind_tb_parent_service_order 
                    WHERE parent_id = %s
                """
                cursor.execute(sql_delete_orders, (parent_id,))
                print(f"已删除家长 {parent_id} 的所有订单数据")

                # 删除kind_tb_video_service_parent中的数据
                sql_delete_video_services = """
                    DELETE FROM kind_tb_video_service_parent 
                    WHERE parent_id = %s
                """
                cursor.execute(sql_delete_video_services, (parent_id,))
                print(f"已删除家长 {parent_id} 的所有视频服务数据")

                self.connection.commit()
                print("删除操作完成")
                return True
        except Exception as e:
            print(f"删除订单数据失败: {e}")
            self.connection.rollback()
            return False

    def clear_wechat_login(self, phone: str) -> bool:
        """清除用户登录微信"""
        try:
            with self.connection.cursor() as cursor:
                # 查询家长信息
                parent_info = self.get_parent_by_phone(phone)
                if not parent_info:
                    print(f"未找到手机号 {phone} 对应的家长信息")
                    return False

                parent_id = parent_info['parent_id']

                # 确认操作
                confirm = input(f"确认清除手机号 {phone} 的微信登录信息吗？(y/N): ").strip().lower()
                if confirm != 'y':
                    print("操作已取消")
                    return False

                # 清空mini_open_id字段
                sql_clear_wechat = """
                    UPDATE kind_tb_parent 
                    SET mini_open_id = NULL, update_time = NOW() 
                    WHERE parent_id = %s
                """
                cursor.execute(sql_clear_wechat, (parent_id,))

                self.connection.commit()
                print(f"已清除手机号 {phone} 的微信登录信息")
                return True
        except Exception as e:
            print(f"清除微信登录信息失败: {e}")
            self.connection.rollback()
            return False

    def display_parent_info(self, parent_info: Dict[str, Any]):
        """显示家长信息"""
        if not parent_info:
            print("未找到家长信息")
            return

        print("\n=== 家长信息 ===")
        print(f"家长ID: {parent_info.get('parent_id')}")
        print(f"姓名: {parent_info.get('parent_name')}")
        print(f"手机号: {parent_info.get('parent_phone')}")
        print(f"微信OpenID: {parent_info.get('mini_open_id')}")
        print(f"孩子ID: {parent_info.get('child_id')}")

    def display_children_info(self, children: List[Dict[str, Any]]):
        """显示孩子信息"""
        if not children:
            print("未找到在校孩子信息")
            return

        print("\n=== 孩子信息 (仅显示在校状态) ===")
        for i, child in enumerate(children, 1):
            # 获取班级和幼儿园名称
            classes_info = self.get_classes_by_id(child.get('classes_id'))
            kindergarten_info = self.get_kindergarten_by_id(child.get('kindergarten_id'))

            classes_name = classes_info.get('classes_name') if classes_info else "未知班级"
            kindergarten_name = kindergarten_info.get('kindergarten_name') if kindergarten_info else "未知幼儿园"

            print(f"\n孩子 {i}:")
            print(f"  姓名: {child.get('child_name')}")
            print(f"  学号: {child.get('child_number')}")
            print(f"  性别: {'男' if child.get('child_sex') == 1 else '女'}")
            print(f"  生日: {child.get('child_birthday')}")
            print(f"  状态: {self.get_child_status_text(child.get('child_status'))}")
            print(f"  幼儿园: {kindergarten_name}")
            print(f"  班级: {classes_name}")

    def display_orders(self, orders: List[Dict[str, Any]]):
        """显示订单信息"""
        print("\n=== 订单信息 (仅显示未过期的订单和孩子当前幼儿园的数据) ===")
        if not orders:
            print("未找到有效的订单信息")
            return

        for i, order in enumerate(orders, 1):
            # 获取班级和幼儿园名称
            classes_info = self.get_classes_by_id(order.get('classes_id'))
            kindergarten_info = self.get_kindergarten_by_id(order.get('kindergarten_id'))

            classes_name = classes_info.get('classes_name') if classes_info else "未知班级"
            kindergarten_name = kindergarten_info.get('kindergarten_name') if kindergarten_info else "未知幼儿园"

            # 解析child_ids
            child_ids = []
            try:
                child_ids = json.loads(order.get('child_ids', '[]'))
            except:
                pass

            print(f"\n订单 {i}:")
            print(f"  订单ID: {order.get('service_order_id')}")
            print(f"  订单号: {order.get('order_no')}")
            print(f"  标题: {order.get('order_title')}")
            print(f"  幼儿园: {kindergarten_name}")
            print(f"  班级: {classes_name}")
            print(f"  关联孩子数量: {len(child_ids)}")
            print(f"  金额: {order.get('order_amount')}")
            print(f"  开始日期: {order.get('start_date')}")
            print(f"  结束日期: {order.get('end_date')}")
            print(
                f"  是否过期: {'否' if order.get('end_date') and order.get('end_date') > datetime.now().date() else '是'}")

    def display_video_services(self, video_services: List[Dict[str, Any]]):
        """显示视频服务信息"""
        print("\n=== 视频服务信息 (仅显示未过期的服务和孩子当前幼儿园的数据) ===")
        if not video_services:
            print("未找到有效的视频服务信息")
            return

        for i, service in enumerate(video_services, 1):
            # 获取班级和幼儿园名称
            classes_info = self.get_classes_by_id(service.get('classes_id'))
            kindergarten_info = self.get_kindergarten_by_id(service.get('kindergarten_id'))

            classes_name = classes_info.get('classes_name') if classes_info else "未知班级"
            kindergarten_name = kindergarten_info.get('kindergarten_name') if kindergarten_info else "未知幼儿园"

            # 解析child_ids
            child_ids = []
            try:
                child_ids = json.loads(service.get('child_ids', '[]'))
            except:
                pass

            print(f"\n视频服务 {i}:")
            print(f"  服务ID: {service.get('video_service_parent_id')}")
            print(f"  订单ID: {service.get('service_order_id')}")
            print(f"  幼儿园: {kindergarten_name}")
            print(f"  班级: {classes_name}")
            print(f"  关联孩子数量: {len(child_ids)}")
            print(f"  开始日期: {service.get('start_date')}")
            print(f"  结束日期: {service.get('end_date')}")
            print(
                f"  是否过期: {'否' if service.get('end_date') and service.get('end_date') > datetime.now().date() else '是'}")

    def get_child_status_text(self, status: int) -> str:
        """获取孩子状态文本"""
        status_map = {
            1: "在校",
            2: "毕业",
            3: "退园"
        }
        return status_map.get(status, "未知状态")

    def display_classes_for_kindergarten(self, kindergarten_id: str):
        """显示幼儿园的所有班级"""
        classes_list = self.get_classes_by_kindergarten(kindergarten_id)
        if not classes_list:
            print("该幼儿园没有班级")
            return

        kindergarten_info = self.get_kindergarten_by_id(kindergarten_id)
        kindergarten_name = kindergarten_info.get('kindergarten_name') if kindergarten_info else "未知幼儿园"

        print(f"\n=== {kindergarten_name} 的班级列表 ===")
        for i, class_info in enumerate(classes_list, 1):
            print(f"{i}. {class_info.get('classes_name')} (ID: {class_info.get('classes_id')})")

    def modify_classes_id_interactive(self, current_kindergarten_id: str, current_classes_id: str) -> Optional[str]:
        """交互式修改classes_id"""
        # 显示当前幼儿园的所有班级
        self.display_classes_for_kindergarten(current_kindergarten_id)

        # 获取当前班级信息
        current_class = self.get_classes_by_id(current_classes_id)
        current_class_name = current_class.get('classes_name') if current_class else "未知班级"

        print(f"\n当前班级: {current_class_name} (ID: {current_classes_id})")

        # 获取用户输入
        while True:
            choice = input("\n请选择要修改的班级编号(输入0取消): ").strip()

            if choice == '0':
                print("取消修改")
                return None

            # 获取班级列表
            classes_list = self.get_classes_by_kindergarten(current_kindergarten_id)

            try:
                choice_idx = int(choice) - 1
                if 0 <= choice_idx < len(classes_list):
                    selected_class = classes_list[choice_idx]
                    print(f"已选择: {selected_class.get('classes_name')} (ID: {selected_class.get('classes_id')})")
                    confirm = input("确认修改? (y/N): ").strip().lower()
                    if confirm == 'y':
                        return selected_class.get('classes_id')
                    else:
                        print("取消修改")
                        return None
                else:
                    print("无效的选择，请重新输入")
            except ValueError:
                print("请输入有效的数字")

    def show_menu(self):
        """显示主菜单"""
        print("\n=== 幼儿园管理系统工具集 ===")
        print("1. 查询家长和孩子信息")
        print("2. 修改班级ID")
        print("3. 删除家长用户数据")
        print("4. 删除订单记录数据")
        print("5. 清除用户登录微信")
        print("0. 退出")

    def run_query_function(self):
        """运行查询功能"""
        # 获取用户输入的手机号
        phone = input("请输入家长手机号: ").strip()
        if not phone:
            print("手机号不能为空")
            return

        # 查询家长信息
        parent_info = self.get_parent_by_phone(phone)
        self.display_parent_info(parent_info)

        if not parent_info:
            return

        parent_id = parent_info['parent_id']

        # 查询孩子信息
        children = self.get_children_by_parent_id(parent_id)
        self.display_children_info(children)

        # 获取孩子当前所在幼儿园的ID列表
        kindergarten_ids = list(set([child['kindergarten_id'] for child in children]))

        # 查询订单信息
        orders = self.get_orders_by_parent_id(parent_id, kindergarten_ids)
        self.display_orders(orders)

        # 查询视频服务信息
        video_services = self.get_video_services_by_parent_id(parent_id, kindergarten_ids)
        self.display_video_services(video_services)

    def run_modify_function(self):
        """运行修改班级ID功能"""
        # 获取用户输入的手机号
        phone = input("请输入家长手机号: ").strip()
        if not phone:
            print("手机号不能为空")
            return

        # 查询家长信息
        parent_info = self.get_parent_by_phone(phone)
        if not parent_info:
            print(f"未找到手机号 {phone} 对应的家长信息")
            return

        parent_id = parent_info['parent_id']

        # 查询孩子信息
        children = self.get_children_by_parent_id(parent_id)
        self.display_children_info(children)

        # 获取孩子当前所在幼儿园的ID列表
        kindergarten_ids = list(set([child['kindergarten_id'] for child in children]))

        # 查询订单信息
        orders = self.get_orders_by_parent_id(parent_id, kindergarten_ids)
        self.display_orders(orders)

        # 查询视频服务信息
        video_services = self.get_video_services_by_parent_id(parent_id, kindergarten_ids)
        self.display_video_services(video_services)

        # 询问是否需要修改classes_id
        modify = input("\n是否需要修改classes_id? (y/N): ").strip().lower()
        if modify == 'y':
            # 修改订单classes_id
            if orders:
                order_modify = input("是否修改订单的classes_id? (y/N): ").strip().lower()
                if order_modify == 'y':
                    for order in orders:
                        print(f"\n处理订单: {order.get('order_no')}")
                        new_classes_id = self.modify_classes_id_interactive(
                            order.get('kindergarten_id'),
                            order.get('classes_id')
                        )
                        if new_classes_id:
                            self.update_order_classes_id(
                                order['service_order_id'],
                                new_classes_id,
                                order['child_ids']
                            )

            # 修改视频服务classes_id
            if video_services:
                video_modify = input("是否修改视频服务的classes_id? (y/N): ").strip().lower()
                if video_modify == 'y':
                    for service in video_services:
                        print(f"\n处理视频服务: {service.get('video_service_parent_id')}")
                        new_classes_id = self.modify_classes_id_interactive(
                            service.get('kindergarten_id'),
                            service.get('classes_id')
                        )
                        if new_classes_id:
                            self.update_video_service_classes_id(
                                service['video_service_parent_id'],
                                new_classes_id,
                                service['child_ids']
                            )

            print("修改完成")

    def run(self):
        """运行主程序"""
        if not self.connect():
            return

        try:
            while True:
                self.show_menu()
                choice = input("请选择功能 (0-5): ").strip()

                if choice == '0':
                    print("感谢使用，再见！")
                    break
                elif choice == '1':
                    self.run_query_function()
                elif choice == '2':
                    self.run_modify_function()
                elif choice == '3':
                    phone = input("请输入要删除的家长手机号: ").strip()
                    if phone:
                        self.delete_parent_data(phone)
                elif choice == '4':
                    phone = input("请输入要删除订单的家长手机号: ").strip()
                    if phone:
                        self.delete_order_data(phone)
                elif choice == '5':
                    phone = input("请输入要清除微信登录的家长手机号: ").strip()
                    if phone:
                        self.clear_wechat_login(phone)
                else:
                    print("无效的选择，请重新输入")

                input("\n按回车键继续...")

        finally:
            self.disconnect()


#
# 使用示例
if __name__ == "__main__":
    # 数据库配置 - 请根据实际情况修改
    db_config = {
        'host': '47.104.97.220',
        'user': 'tongban',
        'password': 'rWFXHT8HCcDJcbZP',
        'database': 'tongban',
        'port': 3306
    }

    # 创建工具实例并运行
    tool = KindergartenDBTool(**db_config)
    tool.run()